﻿Imports System.Data
Imports System.Data.Odbc
Imports System.Text
Imports System.Collections.Generic
Imports WSEntidades
Imports WSFactory
Imports MySql.Data.MySqlClient

Public Class PerfilDAL
    Private cadConex As String = ""

    Public Sub New(ByVal pCadConex As String)
        Me.cadConex = pCadConex
    End Sub

#Region "FuncionesUtilizables"
    Public Function existePerfil(ByVal pPerfil As String) As Boolean
        Dim id As Int16 = 0
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "SELECT COUNT(*) " & _
                                            "FROM perfiles " & _
                                            "WHERE Perfil = ?Perfil;"
                    myComm.Parameters.Clear()
                    myComm.Parameters.AddWithValue("?Perfil", pPerfil)
                    myConn.Open()
                    id = Convert.ToInt16(myComm.ExecuteScalar())
                End Using
            End Using
            Return (id > 0)
        Catch ex As Exception
            Throw
        Finally
            id = Nothing
        End Try
    End Function

    Public Function registraPerfil(ByVal pPerfil As perfilBE, ByRef idPerfil As Integer) As Boolean
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "INSERT INTO perfiles(Perfil, Activo) " & _
                                            "VALUES (@Perfil, @Activo);"
                    myComm.CommandType = System.Data.CommandType.Text
                    myComm.Parameters.Clear()
                    myComm.Parameters.AddWithValue("@Perfil", pPerfil.Perfil)
                    Select Case pPerfil.Activo
                        Case perfilBE.ActivoPerfil.Activo
                            myComm.Parameters.AddWithValue("@Activo", "S")
                        Case perfilBE.ActivoPerfil.Inactivo
                            myComm.Parameters.AddWithValue("@Activo", "N")
                        Case Else
                            myComm.Parameters.AddWithValue("@Activo", "N")
                    End Select
                    myConn.Open()
                    If (myComm.ExecuteNonQuery() = 1) Then
                        myComm.CommandText = "SELECT @@identity; "
                        idPerfil = CType(myComm.ExecuteScalar(), Int32)
                        Return True
                    Else
                        Return False
                    End If
                End Using
            End Using
        Catch ex As Exception
            Throw
        End Try
    End Function

    Public Function registraPermisos(ByVal ProcPer As List(Of ProcPerBE), ByRef idPerfil As Integer) As Boolean
        Dim numRegistros As Int16 = 0
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "INSERT INTO permisos(idPerfil, idProceso, Total, Consulta, Negar) " & _
                                            "VALUES (@idPerfil, @idProceso, @Total, @Consulta, @Negar);"
                    myComm.CommandType = System.Data.CommandType.Text
                    myConn.Open()
                    For Each Proceso As ProcPerBE In ProcPer
                        myComm.Parameters.Clear()
                        myComm.Parameters.AddWithValue("@idPerfil", idPerfil)
                        myComm.Parameters.AddWithValue("@idProceso", Proceso.idProceso)
                        Select Case Proceso.Total
                            Case ProcPerBE.TotalPermiso.Si
                                myComm.Parameters.AddWithValue("@Total", "S")
                            Case ProcPerBE.TotalPermiso.No
                                myComm.Parameters.AddWithValue("@Total", "N")
                            Case Else
                                myComm.Parameters.AddWithValue("@Total", "N")
                        End Select
                        Select Case Proceso.Consulta
                            Case ProcPerBE.ConsultaPermiso.Si
                                myComm.Parameters.AddWithValue("@Consulta", "S")
                            Case ProcPerBE.ConsultaPermiso.No
                                myComm.Parameters.AddWithValue("@Consulta", "N")
                            Case Else
                                myComm.Parameters.AddWithValue("@Consulta", "N")
                        End Select
                        Select Case Proceso.Negar
                            Case ProcPerBE.NegarPermiso.Si
                                myComm.Parameters.AddWithValue("@Negar", "S")
                            Case ProcPerBE.NegarPermiso.No
                                myComm.Parameters.AddWithValue("@Negar", "N")
                            Case Else
                                myComm.Parameters.AddWithValue("@Negar", "N")
                        End Select
                        If (myComm.ExecuteNonQuery() = 1) Then
                            numRegistros = numRegistros + 1
                        Else
                            Exit For
                        End If
                    Next
                End Using
            End Using
            
            Return (numRegistros = ProcPer.Count)
        Catch ex As Exception
            Throw
        Finally
            numRegistros = Nothing
        End Try
    End Function

    Public Function registraPermiso(ByVal Proceso As ProcPerBE, ByRef idPerfil As Integer) As Boolean
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "INSERT INTO permisos(idPerfil, idProceso, Total, Consulta, Negar) " & _
                                            "VALUES (@idPerfil, @idProceso, @Total, @Consulta, @Negar);"
                    myComm.CommandType = System.Data.CommandType.Text
                    myComm.Parameters.Clear()
                    myComm.Parameters.AddWithValue("@idPerfil", idPerfil)
                    myComm.Parameters.AddWithValue("@idProceso", Proceso.idProceso)
                    Select Case Proceso.Total
                        Case ProcPerBE.TotalPermiso.Si
                            myComm.Parameters.AddWithValue("@Total", "S")
                        Case ProcPerBE.TotalPermiso.No
                            myComm.Parameters.AddWithValue("@Total", "N")
                        Case Else
                            myComm.Parameters.AddWithValue("@Total", "N")
                    End Select
                    Select Case Proceso.Consulta
                        Case ProcPerBE.ConsultaPermiso.Si
                            myComm.Parameters.AddWithValue("@Consulta", "S")
                        Case ProcPerBE.ConsultaPermiso.No
                            myComm.Parameters.AddWithValue("@Consulta", "N")
                        Case Else
                            myComm.Parameters.AddWithValue("@Consulta", "N")
                    End Select
                    Select Case Proceso.Negar
                        Case ProcPerBE.NegarPermiso.Si
                            myComm.Parameters.AddWithValue("@Negar", "S")
                        Case ProcPerBE.NegarPermiso.No
                            myComm.Parameters.AddWithValue("@Negar", "N")
                        Case Else
                            myComm.Parameters.AddWithValue("@Negar", "N")
                    End Select
                    myConn.Open()
                    If (myComm.ExecuteNonQuery() = 1) Then
                        Return True
                    Else
                        Return False
                    End If
                End Using
            End Using
        Catch ex As Exception
            Throw
        End Try
    End Function

    Public Function actualizaPerfil(ByVal pPerfil As perfilBE) As Boolean
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "UPDATE perfiles " & _
                                            "SET Activo = @Activo WHERE Perfil=@Perfil; "
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.Clear()
                    Select Case pPerfil.Activo
                        Case perfilBE.ActivoPerfil.Activo
                            myComm.Parameters.AddWithValue("@Activo", "S")
                        Case perfilBE.ActivoPerfil.Inactivo
                            myComm.Parameters.AddWithValue("@Activo", "N")
                    End Select
                    myComm.Parameters.AddWithValue("@Perfil", pPerfil.Perfil)
                    myConn.Open()
                    If (myComm.ExecuteNonQuery() >= 1) Then
                        Return True
                    Else
                        Return False
                    End If
                End Using
            End Using
        Catch ex As Exception
            Throw
        End Try
    End Function

    Public Function actualizaPermisos(ByVal Procesos As List(Of ProcPerBE), ByVal idPerfil As Integer) As Boolean
        Dim band As Boolean = True
        Dim strQueryExiste As String = "SELECT COUNT(*) " &
                                        "FROM permisos " & _
                                        "WHERE idPerfil=@idPerfil AND idProceso=@idProceso;"
        Try
            For Each Proceso As ProcPerBE In Procesos
                If (band = True) Then
                    Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                        Using myComm As MySqlCommand = myConn.CreateCommand()
                            myComm.CommandText = "UPDATE permisos SET Total = @Total, Consulta = @Consulta, Negar = @Negar " & _
                                                    "WHERE idPerfil=@idPerfil AND idProceso=@idProceso; "
                            myComm.CommandType = CommandType.Text
                            myComm.Parameters.Clear()
                            Select Case Proceso.Total
                                Case ProcPerBE.TotalPermiso.Si
                                    myComm.Parameters.AddWithValue("@Total", "S")
                                Case ProcPerBE.TotalPermiso.No
                                    myComm.Parameters.AddWithValue("@Total", "N")
                            End Select
                            Select Case Proceso.Consulta
                                Case ProcPerBE.ConsultaPermiso.Si
                                    myComm.Parameters.AddWithValue("@Consulta", "S")
                                Case ProcPerBE.ConsultaPermiso.No
                                    myComm.Parameters.AddWithValue("@Consulta", "N")
                            End Select
                            Select Case Proceso.Negar
                                Case ProcPerBE.NegarPermiso.Si
                                    myComm.Parameters.AddWithValue("@Negar", "S")
                                Case ProcPerBE.NegarPermiso.No
                                    myComm.Parameters.AddWithValue("@Negar", "N")
                            End Select
                            myComm.Parameters.AddWithValue("@idPerfil", idPerfil)
                            myComm.Parameters.AddWithValue("@idProceso", Proceso.idProceso)
                            myConn.Open()
                            If (myComm.ExecuteNonQuery() >= 1) Then
                                band = True
                            Else
                                registraPermiso(Proceso, idPerfil)
                            End If
                        End Using
                    End Using
                Else
                    Throw New Exception("No se actualizaron correctamente los permisos.")
                End If
            Next
            Return True
        Catch ex As Exception
            Throw
        End Try
    End Function

    Public Function getPerfil(ByVal IdPerfil As Integer) As List(Of perfilpermiBE)
        Dim tmpLsPerfil As List(Of perfilpermiBE) = New List(Of perfilpermiBE)
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "SELECT * FROM permisos " & _
                    "right join procesos on " & _
                    "permisos.idProceso = procesos.idproceso " & _
                    "left join perfiles on " & _
                    "permisos.IdPerfil = perfiles.idPerfil WHERE (permisos.idPerfil=@IdPerfil or permisos.idPerfil is null); "
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.AddWithValue("@IdPerfil", IdPerfil)
                    myConn.Open()
                    Using myDR As MySqlDataReader = myComm.ExecuteReader()
                        While myDR.Read()
                            Dim Permiso As perfilpermiBE = PerfilPermiFact.Build(myDR)
                            If (Not Permiso Is Nothing) Then
                                tmpLsPerfil.Add(Permiso)
                            End If
                            Permiso = Nothing
                        End While
                    End Using
                End Using
            End Using
            Return tmpLsPerfil

        Catch ex As Exception
            Throw
        Finally
            tmpLsPerfil = Nothing
        End Try
    End Function

    Public Function getListadoPerfil() As List(Of perfilBE)
        Dim PerfilesLs As List(Of perfilBE) = New List(Of perfilBE)
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "SELECT IdPerfil, Perfil, Activo " & _
                                           "FROM perfiles;"
                    myComm.CommandType = CommandType.Text
                    myConn.Open()
                    Using myDR As MySqlDataReader = myComm.ExecuteReader()
                        While myDR.Read()
                            Dim Perfil As perfilBE = PerfilFact.Build(myDR)
                            If (Not Perfil Is Nothing) Then
                                PerfilesLs.Add(Perfil)
                            End If
                            Perfil = Nothing
                        End While
                    End Using
                End Using
            End Using
            Return PerfilesLs
        Catch ex As Exception
            Throw
        Finally
            PerfilesLs = Nothing
        End Try
    End Function
#End Region
End Class
